﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data.Common;
using System.Data;

namespace DAO
{
    public class SanPhamDAO
    {
        public int ThemSanPham(SanPhamDTO spDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "INSERT INTO SanPham(TenSP,DonGiaTV,DonGiaKL,QuyCach,DVT,SLTon,DiemCV) VALUES(@TenSP,@DonGiaTV,@DonGiaKL,@QuyCach,@DVT,@SLTon,@DiemCV)";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@TenSP", OleDbType.VarChar);
            para.Value = spDTO.TenSP;
            para = cmd.Parameters.Add("@DonGiaTV", OleDbType.Double);
            para.Value = spDTO.DonGiaTV;
            para = cmd.Parameters.Add("@DonGiaKL", OleDbType.Double);
            para.Value = spDTO.DonGiaKL;
            para = cmd.Parameters.Add("@QuyCach", OleDbType.VarChar);
            para.Value = spDTO.QuyCach;
            para = cmd.Parameters.Add("@DVT", OleDbType.VarChar);
            para.Value = spDTO.DVT;
            para = cmd.Parameters.Add("@SLTon", OleDbType.Integer);
            para.Value = spDTO.SLTon;
            para = cmd.Parameters.Add("@DiemCV", OleDbType.Integer);
            para.Value = spDTO.DiemCV;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;

        }
        //---------------------------------------------------------------------------------
        public List<SanPhamDTO> LayDSSanPham()
        {
            string sql = "SELECT * FROM SanPham";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
           // var dt = new DataTable();
            //OleDbDataAdapter da = new OleDbDataAdapter(cmd);
           // da.Fill(dt);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> danhSach = new List<SanPhamDTO>();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    SanPhamDTO spDTO = new SanPhamDTO();
            //    spDTO.MaSP = (int)dr[0];
            //    spDTO.TenSP = dr[1].ToString();
            //    spDTO.DonGiaKL = dr[2] == DBNull.Value ? 0 : (int)dr[2];
            //    spDTO.DonGiaTV = (int)dr[3];
            //    spDTO.QuyCach = dr[4].ToString();
            //    spDTO.DVT = dr[5].ToString();
            //    spDTO.SLTon = (int)dr[6];
            //    spDTO.DiemCV = (int)dr[7];
            //    danhSach.Add(spDTO);
            //}
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);            
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
                danhSach.Add(spDTO);
            }
            conn.Close();
            return danhSach;
        }
        //---------------------------------------------------------------------------------
        public int XoaSanPham(int MaSP)
        {
            string sql = "delete from SanPham where MaSP=" + MaSP.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            // conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }
        //---------------------------------------------------------------------------------
        public List<SanPhamDTO> ChiTietSanPham(int masp)
        {
            string sql = "SELECT * FROM SanPham where MaSP="+masp.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);            
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> danhSach = new List<SanPhamDTO>();            
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
                danhSach.Add(spDTO);
            }
            conn.Close();
            return danhSach;
        }
        public List<SanPhamDTO> SanPhamNotInCTHoaDon(int MaHD)
        {
            string sql = "SELECT * FROM SanPham where SLTon>0 and MaSP not in (select MaSP from CTHoaDon where MaHD="+MaHD.ToString()+")";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> danhSach = new List<SanPhamDTO>();
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
                danhSach.Add(spDTO);
            }
            conn.Close();
            return danhSach;
        }
        public SanPhamDTO LayTTSP(int masp)
        {
            string sql = "SELECT * FROM SanPham where MaSP=" + masp.ToString();
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
            conn.Close();
            return spDTO;
        }
        //----------------------------------------------------------------
        public int CapNhatSanPham(SanPhamDTO spDTO)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "UPDATE SanPham set TenSP=@TenSP,DonGiaTV=@DonGiaTV,DonGiaKL=@DonGiaKL,QuyCach=@QuyCach,DVT=@DVT,DiemCV=@DiemCV where MaSP="+spDTO.MaSP.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@TenSP", OleDbType.VarChar);
            para.Value = spDTO.TenSP;
            para = cmd.Parameters.Add("@DonGiaTV", OleDbType.Double);
            para.Value = spDTO.DonGiaTV;
            para = cmd.Parameters.Add("@DonGiaKL", OleDbType.Double);
            para.Value = spDTO.DonGiaKL;
            para = cmd.Parameters.Add("@QuyCach", OleDbType.VarChar);
            para.Value = spDTO.QuyCach;
            para = cmd.Parameters.Add("@DVT", OleDbType.VarChar);
            para.Value = spDTO.DVT;        
            para = cmd.Parameters.Add("@DiemCV", OleDbType.Integer);
            para.Value = spDTO.DiemCV;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        //---------------------------------------------------------------------------------------------
        public int CapNhatSLSanPham(int MaSP,int sl)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "UPDATE SanPham set SLTon=SlTon-@sl where MaSP=" + MaSP.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;
            para = new OleDbParameter("@sl", OleDbType.Integer);
            para.Value = sl;
            cmd.Parameters.Add(para);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        //--------------------------------------------------------------------------------------------------------------
        public List<SanPhamDTO> TimSanPham(string tensp)
        {
            string sql = "SELECT * FROM SanPham where TenSP LIKE '%" + tensp +"%' or MaSP LIKE '%"+ tensp +"%'";            
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> danhSach = new List<SanPhamDTO>();
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetInt32(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.DonGiaTV = reader.GetDouble(2);
                spDTO.DonGiaKL = reader.GetDouble(3);
                spDTO.QuyCach = reader.GetString(4);
                spDTO.DVT = reader.GetString(5);
                spDTO.SLTon = reader.GetInt32(6);
                spDTO.DiemCV = reader.GetInt32(7);
                danhSach.Add(spDTO);
            }
            conn.Close();
            return danhSach;
        }
        //-----------------------------------------------------------------------------------------------
        public OleDbDataAdapter LayBaoCaoNhapXuatTon(DateTime bd, DateTime kt)
        {
            string sql = "select MaSp,TenSp from SanPham";
            /*string sql = @"select SanPham.MaSP, SanPham.TenSP, SoDu.SoDu
                          FROM SanPham inner JOIN SoDu ON SanPham.MaSP = SoDu.MaSP
                           where month(SoDu.Ngay) = month(#{0}#) and year(SoDu.Ngay) = year(#{0}#)";*/
            /*string sql = @"select sp.MaSP, sp.TenSP, sd.SoDu, sd.Ngay
                          from SanPham sp inner join SoDu sd on sp.MaSP=sd.MaSp
                           where sd.Ngay between #{0}# and #{1}#";*/
            sql = string.Format(sql, bd.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            conn.Close();
            return da;
        }
        public int SoDuDK(int MaSP, DateTime dt)
        {
            string sql = @"select SoDu 
                           from SoDu 
                           where MaSP=" + MaSP.ToString() + " and month(Ngay)=month(#{0}#)  and year(SoDu.Ngay) = year(#{0}#)";
            sql = string.Format(sql, dt.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }

        public string LayTenSP(int maSP)
        {
            string sql = " SELECT TenSP FROM SanPham WHERE MaSP = " + maSP;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            string kq = (string)cmd.ExecuteScalar();
            return kq;

        }
       
        
        public int TongNhap(int MaSP, DateTime bd, DateTime kt)
        {
            string sql = "select sum(SoLuong) from CTPhieuNhap where MaSP=" + MaSP.ToString() + " and NgayNhap between #{0}# and #{1}#";
            sql = string.Format(sql, bd.ToShortDateString(), kt.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }
        public int TongXuat(int MaSP, DateTime bd, DateTime kt)
        {
            string sql = "select sum(SoLuong) from CTHoaDon inner join HoaDon on CTHoaDon.MaHD=HoaDon.MaHD where MaSP=" + MaSP.ToString() + " and NgayHD between #{0}# and #{1}#";
            sql = string.Format(sql, bd.ToShortDateString(), kt.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }
    }
}
